package com.za.plugin.transfer.form;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlASTVisitorAdapter;

import java.lang.reflect.Field;

public class ChangeDivFormTransfer implements FormTransfer {
    @Override
    public boolean isSupport(String sql) {
        return sql.contains("/");
    }

    @Override
    public String transfer(String sql) {
        return  changeDivSql(sql);
    }
    private static String changeDivSql(String sql) {
        MySqlStatementParser parser = new MySqlStatementParser(sql.toLowerCase());
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) parser.parseStatement();
        sqlSelectStatement.accept(new MySqlASTVisitorAdapter() {
            @Override
            public boolean visit(SQLBinaryOpExpr expr) {
                if (expr instanceof SQLBinaryOpExpr) {
                    SQLBinaryOperator operator = (expr).getOperator();
                    if (operator == SQLBinaryOperator.Divide) {

                        String left = SQLUtils.toMySqlString((expr).getLeft());
                        String right = SQLUtils.toMySqlString((expr).getRight());
                        StringBuilder sb = new StringBuilder();
                        sb.append("case when (").append(right).append(" is null or ").append(right).append("=0 )").append(" then null ")
                                .append(" when ").append(left).append(" is null then null ")
                                .append(" else ").append(SQLUtils.toMySqlString(expr))
                                .append(" end ");
                        SQLExpr sqlExpr = SQLUtils.toSQLExpr(sb.toString());
                        SQLObject parent = expr.getParent();
                        Field[] fields = parent.getClass().getDeclaredFields();
                        try {
                            for (Field field : fields) {
                                field.setAccessible(true);

                                Object o = field.get(parent);
                                if (o == null) {
                                    continue;
                                }
                                /*if (o instanceof List){
                                    for (Object ele : ((List<?>) o)) {
                                        if (SQLBinaryOpExpr.class.isAssignableFrom(ele.getClass())) {
                                            SQLBinaryOpExpr obj = (SQLBinaryOpExpr) ele;
                                            if (obj.getOperator() == SQLBinaryOperator.Divide) {
//                                                field.set(parent, sqlExpr);
//                                                ele.set
                                            }
                                        }
                                    }
                                    continue;
                                }*/
                                if (SQLBinaryOpExpr.class.isAssignableFrom(o.getClass())) {

                                    SQLBinaryOpExpr obj = (SQLBinaryOpExpr) o;
                                    if (obj.getOperator() == SQLBinaryOperator.Divide) {
                                        field.set(parent, sqlExpr);
                                    }
                                }
                            }
                        } catch (IllegalAccessException e) {
                            throw new RuntimeException(e);
                        }
                    }
                }
                return super.visit(expr);
            }
        });


        return SQLUtils.toMySqlString(sqlSelectStatement);
    }

    public static void main(String[] args) {
        String sql = "SELECT\n" +
                "            a.farm_id farmId,\n" +
                "            a.farm_name farmName,\n" +
                "            a.company_id companyId,\n" +
                "            a.company_name companyName,\n" +
                "            a.company_category_id companyCategoryId,\n" +
                "            a.company_category_name companyCategoryName,\n" +
                "            a.business_category_ids businessCategoryIds,\n" +
                "            a.business_category_names businessCategoryNames,\n" +
                "            a.province_id AS provinceId,\n" +
                "            a.province_name AS provinceName,\n" +
                "            a.city_id AS cityId,\n" +
                "            a.city_name AS cityName,\n" +
                "            a.county_id AS countyId,\n" +
                "            a.county_name AS countyName,\n" +
                "            a.town_id AS townId,\n" +
                "            a.town_name AS townName,\n" +
                "            a.village_id AS villageId,\n" +
                "            a.village_name AS villageName,\n" +
                "            ifnull(SUM(IF ( a.first_agri_category_id = 1, a.sow_acre, 0 )),0) sowAcreSum,\n" +
                "            SUM( a.mission_used_value ) missionUsedValue,\n" +
                "            SUM( IF ( a.second_agri_category_name = '有机肥', mission_used_value, 0 ) ) orgFertilizer,\n" +
                "            SUM( IF ( a.second_agri_category_name = '化肥', mission_used_value, 0 ) ) cheFertilizer,\n" +
                "            SUM( IF ( a.second_agri_category_name = '水溶性肥料', mission_used_value, 0 ) ) waterSolubleFertilizer,\n" +
                "            SUM( IF ( a.second_agri_category_name != '有机肥' AND a.second_agri_category_name != '化肥' AND a.second_agri_category_name != '水溶性肥料' OR a.second_agri_category_name is null OR a.second_agri_category_name = '', mission_used_value, 0 ) ) otherFertilizer,\n" +
                "            ifnull(round(IF(SUM( IF ( a.second_agri_category_name = '有机肥', a.sow_acre, 0 ) ) = 0,0,SUM( IF ( a.second_agri_category_name = '有机肥', mission_used_value, 0 ) ) / SUM( IF ( a.second_agri_category_name = '有机肥', a.sow_acre, 0 ) )),2),0) AS orgFertilizerPer,\n" +
                "            ifnull(round(IF(SUM( IF ( a.second_agri_category_name = '化肥', a.sow_acre, 0 ) ) = 0,0,SUM( IF ( a.second_agri_category_name = '化肥', mission_used_value, 0 ) ) / SUM( IF ( a.second_agri_category_name = '化肥', a.sow_acre, 0 ) )),2),0) AS cheFertilizerPer,\n" +
                "            ifnull(round(IF(SUM( IF ( a.second_agri_category_name = '水溶性肥料', a.sow_acre, 0 ) ) = 0,0,SUM( IF ( a.second_agri_category_name = '水溶性肥料', mission_used_value, 0 ) ) / SUM( IF ( a.second_agri_category_name = '水溶性肥料', a.sow_acre, 0 ) )),2),0) AS waterSolubleFertilizerPer,\n" +
                "            round(IF(SUM(IF\n" +
                "        ( a.second_agri_category_name != '有机肥' AND a.second_agri_category_name != '化肥' AND a.second_agri_category_name != '水溶性肥料' OR a.second_agri_category_name is null OR a.second_agri_category_name = '', a.sow_acre, 0 )\n" +
                "        ) = 0,0,SUM( IF ( a.second_agri_category_name != '有机肥' AND a.second_agri_category_name != '化肥' AND a.second_agri_category_name != '水溶性肥料' OR a.second_agri_category_name is null OR a.second_agri_category_name = '', mission_used_value, 0 ) ) /\n" +
                "                SUM(IF\n" +
                "            ( a.second_agri_category_name != '有机肥' AND a.second_agri_category_name != '化肥' AND a.second_agri_category_name != '水溶性肥料' OR a.second_agri_category_name is null OR a.second_agri_category_name = '', a.sow_acre, 0 )\n" +
                "            )),2) AS otherFertilizerPer,\n" +
                "            ROUND(IF(SUM(a.sow_acre_n) = 0,0,SUM(a.used_n) / SUM(a.sow_acre_n)),2) pureN,\n" +
                "            ROUND(IF(SUM(a.sow_acre_p) = 0,0,SUM(a.used_p) / SUM(a.sow_acre_p)),2) pureP,\n" +
                "            ROUND(IF(SUM(a.sow_acre_k) = 0,0,SUM(a.used_k) / SUM(a.sow_acre_k)),2) pureK\n" +
                "        FROM\n" +
                "            tbl_archive_farm_agri_res_month a";
        System.out.println(changeDivSql(sql));
    }
}
